package Dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import Entity.CourseResource;
import Entity.LearningResource;
import OracleHelper.OracleHelper;

public class LearningResourceDao {
	
	/*向learningResource表中插入数据*/
	public int addLearningResource(LearningResource lr) {
		String sql="insert into learningResource values('"+lr.getResourceID()+"','"+lr.getLabel()+"','"+lr.getResourceName()+"','"+lr.getUploaderID()+"'"
				+ ",'"+lr.getUploadTime()+"','"+lr.getFilePath()+"','"+lr.getThumbNumber()+"','"+lr.getCommentatorID()+"','"+lr.getCommentID()+"',"
						+ "'"+lr.getBriefIntroduce()+"')";
		return new OracleHelper().executeUpdate(sql);		
	}
	
	/*删除一条信息*/
	public int deleteLearningResourceByResourceId(String resourceID) {
		String sql="delete from learningResource where resourceID='"+resourceID+"'";
		return new OracleHelper().executeUpdate(sql);
		
	}
	
	
	/*获取所有学习资源,并按照点赞数排列*/
	public List<LearningResource> showAllLearningResourceOrderByThumbNumber(){
		List<LearningResource> lrlist=new ArrayList<LearningResource>();
		String sql="select * from learningResource order by thumbNumber desc";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try {
			while(rs.next()) {
				LearningResource lr=new LearningResource();
				lr.setResourceID(rs.getString(1));
				lr.setLabel(rs.getString(2));
				lr.setResourceName(rs.getString(3));
				lr.setUploaderID(rs.getString(4));
				lr.setUploadTime(rs.getString(5));
				lr.setFilePath(rs.getString(6));
				lr.setThumbNumber(Integer.parseInt(rs.getString(7)));
				lr.setCommentatorID(rs.getString(8));
				lr.setCommentID(rs.getString(9));
				lr.setBriefIntroduce(rs.getString(10));
				lrlist.add(lr);
			}
		}catch(Exception ex) {
			ex.printStackTrace();
		}
		return lrlist;
		
	}
	
	/*通过标签模糊查询所有相关学习资源,并按照点赞数排序*/
	public List<LearningResource> queryLearningResourceByLabel(String label){
		List<LearningResource> lrlist=new ArrayList<LearningResource>();
		String sql="select * from learningResource where label like '%"+label+"%' order by thumbNumber desc";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try {
			while(rs.next()) {
				LearningResource lr=new LearningResource();
				lr.setResourceID(rs.getString(1));
				lr.setLabel(rs.getString(2));
				lr.setResourceName(rs.getString(3));
				lr.setUploaderID(rs.getString(4));
				lr.setUploadTime(rs.getString(5));
				lr.setFilePath(rs.getString(6));
				lr.setThumbNumber(Integer.parseInt(rs.getString(7)));
				lr.setCommentatorID(rs.getString(8));
				lr.setCommentID(rs.getString(9));
				lr.setBriefIntroduce(rs.getString(10));
				lrlist.add(lr);
			}
		}catch(Exception ex) {
			ex.printStackTrace();
		}
		return lrlist;
		
	}
	
	/*通过上传者的ID查询该上传者上传的所有学习资源*/
	public List<LearningResource> queryLearningResourceByUploaderID(String uploaderID){
		List<LearningResource> lrlist=new ArrayList<LearningResource>();
		String sql="select * from learningResource where uploaderID='"+uploaderID+"'";
		ResultSet rs=OracleHelper.executeQuery(sql);
		try{
			while(rs.next()){
				LearningResource lr=new LearningResource();
				lr.setResourceID(rs.getString(1));
				lr.setLabel(rs.getString(2));
				lr.setResourceName(rs.getString(3));
				lr.setUploaderID(rs.getString(4));
				lr.setUploadTime(rs.getString(5));
				lr.setFilePath(rs.getString(6));
				lr.setThumbNumber(Integer.parseInt(rs.getString(7)));
				lr.setCommentatorID(rs.getString(8));
				lr.setCommentID(rs.getString(9));
				lr.setBriefIntroduce(rs.getString(10));
				lrlist.add(lr);
			}
			OracleHelper.closeConnection();;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return lrlist;			
	}
	
	/*通过资源ID查询该资源*/
	public LearningResource queryLearningResourceByResourceID(String resourceID) {
		String sql="select * from learningResource where resourceID='"+resourceID+"'";
		LearningResource lr=null;
		ResultSet rs=OracleHelper.executeQuery(sql);
		try{
			while(rs.next()){
				lr=new LearningResource();
				lr.setResourceID(rs.getString(1));
				lr.setLabel(rs.getString(2));
				lr.setResourceName(rs.getString(3));
				lr.setUploaderID(rs.getString(4));
				lr.setUploadTime(rs.getString(5));
				lr.setFilePath(rs.getString(6));
				lr.setThumbNumber(Integer.parseInt(rs.getString(7)));
				lr.setCommentatorID(rs.getString(8));
				lr.setCommentID(rs.getString(9));
				lr.setBriefIntroduce(rs.getString(10));
			}
			OracleHelper.closeConnection();;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return lr;
		
	}
	
	/*修改某资源的点赞数(根据点赞表中的记录条数进行修改*/
	public int updateThumbNumberOfLearningResource(String resourceID) {
		String sql="update learningResource set thumbNumber=(select count(*) from thumb where resourceID='"+resourceID+"') where resourceID='"+resourceID+"'";
		return new OracleHelper().executeUpdate(sql);
		
	}
	
	/*获取一共有多少条信息*/
	public int queryRecordCount() {
		String sql = "select count(*) from learningResource";
		Object obj = new OracleHelper().executeSingleQuery(sql);
		return Integer.parseInt(obj.toString());
	}
}
